******SET YOUR DIRECTORY to ...\replication




clear all


///////////////////////////////////////////////////////////////////////////////////////////
///Merge files/////////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
***tetra_corr
import excel using "rawdata\tetraethyl\tetraethyl_lead.xlsx", first
drop source Variable Note
drop if year==1987
rename year year_old
gen year=year+19
tsset year
gen tetraethyl_sum5=tetraethyl+L.tetraethyl+L2.tetraethyl+L3.tetraethyl+L4.tetraethyl+L5.tetraethyl
gen stock_tetraethyl=tetraethyl
forvalues i=1962/2006{
replace stock_tetraethyl=L.stock_tetraethyl+tetraethyl if year==`i'
}
***Crime
merge 1:m year using "intermediatedata\data_ucr_plc.dta"
drop _merge
*****RECODE FIPS NOT MATCHING WITH GEOLOGY
replace fipsplace_00 = 1836003 if fipsplace_00 == 1836010
replace fipsplace_00 = 3481950 if fipsplace_00 == 3482000
replace fipsplace_00 = 3473110 if fipsplace_00 == 3418130 
***Geology
merge m:1 fipsplace_00 using "intermediatedata\data_geology_plc.dta"
drop if _merge !=3
drop _merge
***Geology NCC
merge m:1 fipsplace_00 using "intermediatedata\data_geology_ncc.dta"
drop _merge
***Undevelopable area
merge m:1 fipsplace_00 using "intermediatedata\data_undevelopable.dta"
drop _merge


///////////////////////////////////////////////////////////////////////////////////////////
///Define treatments///////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
***Define tetraethyl corrected by max
sum tetraethyl
gen tetra_corr=tetraethyl/`r(max)'
***Define 0.5 bounds dummies for ph and interactions with lead
gen ph_wtm_45_50=(ph1_plc_wtm_wtm_wtm_r!=. & ph1_plc_wtm_wtm_wtm_r<5)
gen ph_wtm_50_55=(ph1_plc_wtm_wtm_wtm_r>=5 & ph1_plc_wtm_wtm_wtm_r<5.5)
gen ph_wtm_55_60=(ph1_plc_wtm_wtm_wtm_r>=5.5 & ph1_plc_wtm_wtm_wtm_r<6)
gen ph_wtm_60_65=(ph1_plc_wtm_wtm_wtm_r>=6 & ph1_plc_wtm_wtm_wtm_r<6.5)
gen ph_wtm_65_70=(ph1_plc_wtm_wtm_wtm_r>=6.5 & ph1_plc_wtm_wtm_wtm_r<7)
gen ph_wtm_70_75=(ph1_plc_wtm_wtm_wtm_r>=7 & ph1_plc_wtm_wtm_wtm_r<7.5)
gen ph_wtm_75_80=(ph1_plc_wtm_wtm_wtm_r>=7.5 & ph1_plc_wtm_wtm_wtm_r<8)
gen ph_wtm_80_85=(ph1_plc_wtm_wtm_wtm_r>=8 & ph1_plc_wtm_wtm_wtm_r!=.)
gen ph_0_45_50=(ph1_plc_wtm_wtm_0_r!=. & ph1_plc_wtm_wtm_0_r<5)
gen ph_0_50_55=(ph1_plc_wtm_wtm_0_r>=5 & ph1_plc_wtm_wtm_0_r<5.5)
gen ph_0_55_60=(ph1_plc_wtm_wtm_0_r>=5.5 & ph1_plc_wtm_wtm_0_r<6)
gen ph_0_60_65=(ph1_plc_wtm_wtm_0_r>=6 & ph1_plc_wtm_wtm_0_r<6.5)
gen ph_0_65_70=(ph1_plc_wtm_wtm_0_r>=6.5 & ph1_plc_wtm_wtm_0_r<7)
gen ph_0_70_75=(ph1_plc_wtm_wtm_0_r>=7 & ph1_plc_wtm_wtm_0_r<7.5)
gen ph_0_75_80=(ph1_plc_wtm_wtm_0_r>=7.5 & ph1_plc_wtm_wtm_0_r<8)
gen ph_0_80_85=(ph1_plc_wtm_wtm_0_r>=8 & ph1_plc_wtm_wtm_0_r!=.)
foreach var of varlist ph_*{
gen inter_tlX`var'=`var'*tetra_corr
}
foreach var of varlist ph_*{
gen inter_tlFR`var'=`var'/tetra_corr
}


///////////////////////////////////////////////////////////////////////////////////////////
///Define regions//////////////////////////////////////////////////////////////////////////
///////////////////////////////////////////////////////////////////////////////////////////
***Generate state/regions
gen state=""
replace state="AL" if STATEFP00==    01 
replace state="AZ" if STATEFP00== 	04 	
replace state="AR" if STATEFP00== 	05 	
replace state="CA" if STATEFP00== 	06 	
replace state="CO" if STATEFP00== 	08 	
replace state="CT" if STATEFP00== 	09 	
replace state="DE" if STATEFP00== 	10 
replace state="DC" if STATEFP00== 	11 	
replace state="FL" if STATEFP00== 	12 	
replace state="GA" if STATEFP00== 	13 	
replace state="ID" if STATEFP00== 	16 	
replace state="IL" if STATEFP00== 	17 	
replace state="IN" if STATEFP00== 	18 
replace state="IA" if STATEFP00== 	19 	
replace state="KS" if STATEFP00== 	20 
replace state="KY" if STATEFP00== 	21 	
replace state="LA" if STATEFP00== 	22 
replace state="ME" if STATEFP00== 	23 	
replace state="MD" if STATEFP00== 	24 
replace state="MA" if STATEFP00== 	25 	
replace state="MI" if STATEFP00== 	26 
replace state="MN" if STATEFP00== 	27 	
replace state="MS" if STATEFP00== 	28 	
replace state="MO" if STATEFP00==    29 
replace state="MT" if STATEFP00== 	30 	
replace state="NE" if STATEFP00== 	31 	
replace state="NV" if STATEFP00== 	32 	
replace state="NH" if STATEFP00== 	33 
replace state="NJ" if STATEFP00== 	34 	
replace state="NM" if STATEFP00== 	35 
replace state="NY" if STATEFP00== 	36 
replace state="NC" if STATEFP00== 	37 	
replace state="ND" if STATEFP00== 	38 	
replace state="OH" if STATEFP00== 	39 
replace state="OK" if STATEFP00== 	40 	
replace state="OR" if STATEFP00== 	41 	
replace state="PA" if STATEFP00== 	42 	
replace state="RI" if STATEFP00== 	44 	
replace state="SC" if STATEFP00== 	45 	
replace state="SD" if STATEFP00== 	46 	
replace state="TN" if STATEFP00== 	47 	
replace state="TX" if STATEFP00== 	48 	
replace state="UT" if STATEFP00== 	49 
replace state="VT" if STATEFP00== 	50 
replace state="VA" if STATEFP00== 	51 	
replace state="WA" if STATEFP00== 	53 	
replace state="WV" if STATEFP00== 	54 
replace state="WI" if STATEFP00== 	55 	
replace state="WY" if STATEFP00== 	56 	
gen cdiv="NE" if STATEFP00==09| STATEFP00==23 | STATEFP00==25 | STATEFP00==33 ///
 | STATEFP00==44| STATEFP00==50
replace cdiv="MD" if STATEFP00==34 | STATEFP00==36 | STATEFP00==42
replace cdiv="ENC" if STATEFP00==17 | STATEFP00==18 | STATEFP00==26 | STATEFP00==39 ///
 | STATEFP00==55
replace cdiv="WNC" if STATEFP00==19 | STATEFP00==20 | STATEFP00==27 | STATEFP00==29 ///
 | STATEFP00==31 | STATEFP00==38 | STATEFP00==46
replace cdiv="SA" if STATEFP00==10 | STATEFP00==12 | STATEFP00==13 | STATEFP00==24 ///
 | STATEFP00==37 | STATEFP00==45 | STATEFP00==51 | STATEFP00==11 | STATEFP00==54
replace cdiv="ESC" if STATEFP00==1 | STATEFP00==21 | STATEFP00==28 | STATEFP00==47
replace cdiv="WSC" if STATEFP00==5 | STATEFP00==22 | STATEFP00==40 | STATEFP00==48
replace cdiv="MO" if STATEFP00==4 | STATEFP00==8 | STATEFP00==16 | STATEFP00==30 ///
 | STATEFP00==32 | STATEFP00==35 | STATEFP00==49 | STATEFP00==56
 replace cdiv="PA" if STATEFP00==6 | STATEFP00==41 | STATEFP00==53
egen cdivcode=group(cdiv)
gen creg="W" if region=="PA" | region=="MO"
replace creg="MW" if region=="WNC" | region=="ENC"
replace creg="S" if region=="WSC" | region=="ESC" | region=="SA"
replace creg="NE" if region=="MD" | region=="NE" 
egen cregcode=group(creg)

drop if FMSA==. & year==.
***Save
save "intermediatedata\int_cc_data.dta", replace



*********PREPARE BAUMSNOW Data for merge with int_cc_data
use "rawdata\BaumSnow\msa-final.dta", clear

rename msa original_fipsMSA
g FMSA = original_fipsMSA

rename placefips original_FPLACE
g FPLACE = original_FPLACE
sort FMSA year

replace FPLACE = 99059 if original_fipsMSA == 500 

replace FPLACE = 99245 if original_fipsMSA == 600 

replace FMSA = 730 if original_fipsMSA == 733

replace FMSA = 1120 if original_fipsMSA == 1123

replace FMSA = 1305 if original_fipsMSA == 1303

replace FPLACE = 99215 if original_fipsMSA == 1800 

replace FMSA = 3280 if original_fipsMSA == 3283

replace FPLACE = 35000 if original_fipsMSA == 3600

replace FMSA = 4240 if original_fipsMSA == 4243

replace FPLACE = 46027 if original_fipsMSA == 4280

replace FMSA = 5480 if original_fipsMSA == 5483

replace FMSA = 5520 if original_fipsMSA == 5523

replace FMSA = 6320 if original_fipsMSA == 6323

replace FMSA = 6400 if original_fipsMSA == 6403

replace FMSA = 6480 if original_fipsMSA == 6483 

replace FMSA = 8000 if original_fipsMSA == 8003

replace year = 1900 + year

******************DROP NASHVILLE MSA BECAUSE IT HAS NO POLICE DEP
drop if FMSA == 5360


*****ADD FMSA THAT BAUM-SNOW DOESN'T HAVE
inp
. 34 . "NJ, Newark" . 5640 51000
end

inp
. 6 . "CA, Oakland" . 5775 53000
end

inp
. 34 . "NJ, Bergen-Passaic" . 875 57000
end

merge 1:1 FMSA year using "intermediatedata\int_cc_data.dta"

drop if _merge==1 & year>1959

g myear = - year
sort FMSA myear
bysort FMSA: replace ph1_plc_wtm_wtm_0_r = ph1_plc_wtm_wtm_0_r[_n-1] if year!=2014
bysort FMSA: replace regioncode = regioncode[_n-1] if year!=2014
bysort FMSA: replace megaregioncode = megaregioncode[_n-1] if year!=2014
bysort FMSA: replace fipsplace_00 = fipsplace_00[_n-1] if year!=2014

drop _merge
*****ADD AGRICULTURAL PROD

merge n:1  fipsplace_00 using "rawdata\agprod_forstata.dta", keepusing( total_prod cereal_prod oil_prod root_prod  alfa_pot corn_pot cott_pot soy_pot wheat_pot)



drop _merge
merge 1:1 FMSA year using "rawdata\final county city data book.dta" 
drop if _merge==2

drop _merge



save "intermediatedata\final_cc_data.dta", replace


///Export all data csv for map
use "finaldata\final_cc_data.dta", clear
collapse ph1_plc_wtm_wtm_0_r, by(fipsplace_00)
gen PLCIDFP00=string(fipsplace_00)
replace PLCIDFP00="0"+PLCIDFP00 if fipsplace_00<1000000

outsheet using "intermediatedate\final_cc_data.txt", replace
